//数据库操作
var mysql=require('mysql');
var db=mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'tiantianpeiwan',
});
let playwithbeautymodule={

    'gamepic':function (fn) {
        let sql=`SELECT * FROM p_game `;
        db.query(sql, function (err, data) {
            fn(err, data)
        })
    },
    'headK':function (fn) {
        let sql=` SELECT *,COUNT(*) AS COUNT FROM p_jdtable a LEFT JOIN p_user b ON a.U_co=b.U_co WHERE JD_puon IN('已评价','待评价')  GROUP BY U_co1  ORDER BY COUNT DESC LIMIT 5`;
        db.query(sql, function (err, data) {
            fn(err, data)
        })
    },
    'headK2':function (fn) {
        let sql=` SELECT * FROM p_phlevel `;
        db.query(sql, function (err, data) {
            fn(err, data)
        })
    },
    'gameChance':function (fn) {
        let sql=` SELECT * FROM p_user a LEFT JOIN p_playuser b ON a.U_co=b.U_co LEFT JOIN p_game c ON b.G_id= c.G_id `;
        db.query(sql, function (err, data) {
            fn(err, data)
        })
    },
    'charm':function (fn) {
        let sql=`   SELECT *,c.GA_price*a.GF_num AS money,COUNT(c.GA_price*a.GF_num) AS COUNT FROM p_gift a LEFT JOIN p_user b ON a.U_co=b.U_co LEFT JOIN p_giftattribute c ON a.GF_gagift=c.GA_id  WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(GF_data) GROUP BY U_co1 ORDER BY money DESC LIMIT 5`;
        db.query(sql, function (err, data) {
            fn(err, data)
        })
    },
    'PUDetail':function (fn) {
        let sql=`SELECT *  FROM p_user a LEFT JOIN p_playuser b ON a.\`U_co\`=b.\`U_co\` GROUP BY a.U_co`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'playerNum':function (fn) {
        let sql=`SELECT   COUNT(DISTINCT a.U_names) AS COUNT  FROM p_user a LEFT JOIN p_playuser b ON a.\`U_co\`=b.\`U_co\``;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'gameMoney':function (fn) {
        let sql=`SELECT *,SUM(JD_money * JD_hours) AS money FROM p_jdtable WHERE JD_puon IN('已评价','待评价')`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'search':function (id,fn) {

        let sql=`SELECT *  FROM p_user a LEFT JOIN p_playuser b ON a.\`U_co\`=b.\`U_co\` WHERE U_id LIKE '%${id}%' OR U_names LIKE '%${id}%' GROUP BY a.U_co `;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'gameHot':function (fn) {

        let sql=`SELECT * FROM p_game`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'moneyTotal':function (fn) {

        let sql=`SELECT *,SUM(PU_price*JD_hours) AS money FROM p_jdtable a LEFT JOIN p_playuser b ON a.\`PU_cid\`=b.\`PU_cid\` LEFT JOIN p_game c ON b.\`G_id\`=c.\`G_id\` WHERE JD_puon IN ('已评价','待评价') GROUP BY c.G_id
`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'ban':function (id,state,fn) {
        let sql1=`SELECT U_black FROM p_user WHERE U_id=${id}`
        let sql2=`UPDATE p_user SET U_black =1  WHERE U_id=${id}`;
        let sql3=`UPDATE p_user SET U_black =0  WHERE U_id=${id}`;
        db.query(sql1,function (err,data) {
            if (data[0].U_black==1) {
                db.query(sql3,function (err,data) {
                    console.log("===============Modu");
                    console.log(data);
                    fn(err,data)
                })
            }else {
                db.query(sql2,function (err,data) {
                    console.log("===============Modu");
                    console.log(data);
                    fn(err,data)
                })
            }

            // fn(err,data)
        })
    },
    'top3':function (fn) {
        let sql=`SELECT *,c.GA_price*a.GF_num+e.JD_money*JD_hours AS money FROM p_gift a LEFT JOIN p_user b ON a.U_co1=b.U_co LEFT JOIN p_giftattribute c ON a.GF_gagift=c.GA_id  LEFT JOIN p_playuser
 d ON  b.\`U_co\`=d.\`U_co\` 
 LEFT JOIN p_jdtable e ON e.\`PU_cid\`=d.\`PU_cid\`
 WHERE JD_puon IN ('已评价','待评价')
 ORDER BY money DESC LIMIT 3`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },



}
module.exports=playwithbeautymodule;